import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import { dirname } from 'path';
import bcrypt from 'bcryptjs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const db = new sqlite3.Database('./database.sqlite');

// 创建用户表
db.serialize(() => {
  // 删除现有表
  db.run("DROP TABLE IF EXISTS users");
  db.run("DROP TABLE IF EXISTS courses");
  db.run("DROP TABLE IF EXISTS enrollments");

  // 创建用户表
  db.run(`CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    role TEXT NOT NULL DEFAULT 'student',
    is_active BOOLEAN DEFAULT 1,
    last_login DATETIME,
    password_changed_at DATETIME,
    password_reset_token VARCHAR(255),
    password_reset_expires DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  )`);

  // 创建课程表
  db.run(`CREATE TABLE courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    credits INTEGER NOT NULL,
    capacity INTEGER NOT NULL,
    teacher_id INTEGER,
    status TEXT DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id)
  )`);

  // 创建选课表
  db.run(`CREATE TABLE enrollments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    status TEXT DEFAULT 'enrolled',
    grade DECIMAL(4,1),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    UNIQUE(student_id, course_id)
  )`);

  // 插入测试数据
  const adminPassword = bcrypt.hashSync('admin123', 12);
  const teacherPassword = bcrypt.hashSync('teacher123', 12);
  const studentPassword = bcrypt.hashSync('student123', 12);

  // 插入测试用户
  db.run(`INSERT INTO users (username, password, name, email, role) VALUES 
    ('admin', ?, 'Admin User', 'admin@example.com', 'admin'),
    ('teacher1', ?, 'Teacher One', 'teacher1@example.com', 'teacher'),
    ('student1', ?, 'Student One', 'student1@example.com', 'student')`,
    [adminPassword, teacherPassword, studentPassword],
    (err) => {
      if (err) {
        console.error('Error inserting test users:', err);
      } else {
        console.log('Test users created successfully');
      }
    }
  );

  // 插入测试课程
  db.run(`INSERT INTO courses (code, name, description, credits, capacity, teacher_id) VALUES 
    ('CS101', '计算机导论', '计算机科学基础课程', 3, 50, 2),
    ('CS102', '程序设计基础', 'Python编程入门', 4, 40, 2),
    ('CS201', '数据结构', '基本数据结构与算法', 4, 35, 2)`,
    (err) => {
      if (err) {
        console.error('Error inserting test courses:', err);
      } else {
        console.log('Test courses created successfully');
      }
    }
  );
});

// 关闭数据库连接
db.close((err) => {
  if (err) {
    console.error('Error closing database:', err);
  } else {
    console.log('Database initialized successfully');
  }
});
